using System.Collections.Generic;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using System.Text;
using System.Data.Common;
using System.Data;
using System;

namespace Sinacor.Servico.Bovespa.Contabil.ResourceAccess
{
	public class TipoDespesa: BaseResourceAccess 
    {
        public TipoDespesa() : base(AuthorizationContext.Current.CompanyId) {}

		public void AlterarTipoDespesa(BusinessEntity.TipoDespesa entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("UPDATE TBODESPESA SET DS_DESP_NOTA = :Descricao WHERE CD_DESP_NOTA = :Codigo");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":Descricao", DbType.String, entity.Descricao);
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, entity.Codigo);
                this.DataBase.ExecuteNonQuery(dbCommand);
            }

		}

      	public bool VerificarDuplicidadeDescricao(BusinessEntity.Parameter.FiltroTipoDespesa parameter)
        {
            StringBuilder sql = new StringBuilder();
            object response = null;

            sql.Append(" SELECT COUNT(*) NumeroRegistros FROM TBODESPESA WHERE DS_DESP_NOTA = :Descricao");
            
            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":Descricao", DbType.String, parameter.Descricao);
                response = this.DataBase.ExecuteScalar(dbCommand);
            }

            if (Convert.ToInt32(response) > 0)
                return true;
            else
                return false;
        }

        public List<BusinessEntity.TipoDespesa> ConsultarTiposDespesas(BusinessEntity.Parameter.FiltroTipoDespesa parameter)
        {
            List<BusinessEntity.TipoDespesa> response = new List<Sinacor.Servico.Bovespa.Contabil.BusinessEntity.TipoDespesa>();
            BusinessEntity.TipoDespesa tipoDespesa;
            StringBuilder sql = new StringBuilder();

            sql.Append(" SELECT CD_DESP_NOTA Codigo, DS_DESP_NOTA Descricao FROM TBODESPESA WHERE 1=1 ");
            
            if ((parameter.Codigo != null)&&(parameter.Codigo != string.Empty))
            {
                sql.Append("AND CD_DESP_NOTA = :Codigo ");
            }

            if ((parameter.Descricao != null)&&(parameter.Descricao != string.Empty))
            {
                sql.Append("AND DS_DESP_NOTA LIKE '%'||:Descricao||'%'");
            }
            		 
            sql.Append(" ORDER BY CD_DESP_NOTA ");            

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                if ((parameter.Codigo != null) && (parameter.Codigo != string.Empty))
                {
                    this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, parameter.Codigo);
                }

                if ((parameter.Descricao != null) && (parameter.Descricao != string.Empty))
                {
                    this.DataBase.AddInParameter(dbCommand, ":Descricao", DbType.String, parameter.Descricao);
                }
               
                response = this.FindAll<BusinessEntity.TipoDespesa>(dbCommand,
                    reader =>
                    {
                        tipoDespesa = new BusinessEntity.TipoDespesa();

                        tipoDespesa.Codigo = reader.GetValue<string>("Codigo");
                        tipoDespesa.Descricao = reader.GetValue<string>("Descricao");

                        return tipoDespesa;
                    });

                return response;
            }
		}
 
  		public List<BusinessEntity.TipoDespesa> ConsultarTiposDespesasPeriodo(BusinessEntity.Parameter.FiltroTipoDespesaPeriodo parameter)
        {
            List<BusinessEntity.TipoDespesa> response = new List<Sinacor.Servico.Bovespa.Contabil.BusinessEntity.TipoDespesa>();
            BusinessEntity.TipoDespesa tipoDespesa;
            StringBuilder sql = new StringBuilder();

            sql.Append(" SELECT DISTINCT A.CD_DESP_NOTA Codigo, A.DS_DESP_NOTA Descricao ");
            sql.Append(" FROM TBODESPESA A LEFT JOIN TBODESPNO B ");
            sql.Append(" ON A.CD_DESP_NOTA = B.CD_DESP_NOTA WHERE 1=1 ");
            
            if ((parameter.Codigo != null)&&(parameter.Codigo != string.Empty))
            {
                sql.Append("AND A.CD_DESP_NOTA = :Codigo ");
            }

            if ((parameter.Descricao != null)&&(parameter.Descricao != string.Empty))
            {
                sql.Append("AND A.DS_DESP_NOTA LIKE '%'||:Descricao||'%'");
            }
            		 
            if (parameter.PregaoInicial != null)
            {
                sql.Append("AND B.DT_PREINI >= :PregaoInicial ");
            }
            		 
            if (parameter.PregaoFinal != null)
            {
                sql.Append("AND B.B.DT_PREFIM <= :PregaoFinal ");
            }

            sql.Append(" ORDER BY A.CD_DESP_NOTA ");            

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                if ((parameter.Codigo != null) && (parameter.Codigo != string.Empty))
                {
                    this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, parameter.Codigo);
                }

                if ((parameter.Descricao != null) && (parameter.Descricao != string.Empty))
                {
                    this.DataBase.AddInParameter(dbCommand, ":Descricao", DbType.String, parameter.Descricao);
                }

                if (parameter.PregaoInicial != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":PregaoInicial", DbType.DateTime, parameter.PregaoInicial);
                }

                if (parameter.PregaoFinal != null)
                {
                    this.DataBase.AddInParameter(dbCommand, ":PregaoFinal", DbType.DateTime, parameter.PregaoFinal);
                }
                
                response = this.FindAll<BusinessEntity.TipoDespesa>(dbCommand,
                    reader =>
                    {
                        tipoDespesa = new BusinessEntity.TipoDespesa();

                        tipoDespesa.Codigo = reader.GetValue<string>("Codigo");
                        tipoDespesa.Descricao = reader.GetValue<string>("Descricao");

                        return tipoDespesa;
                    });

                return response;
            }
		}

		public void ExcluirTipoDespesa(BusinessEntity.Parameter.FiltroTipoDespesaChave parameter)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("DELETE FROM TBODESPESA WHERE CD_DESP_NOTA = :Codigo");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, parameter.Codigo);
                this.DataBase.ExecuteNonQuery(dbCommand);
            }

		}

		public void IncluirTipoDespesa(BusinessEntity.TipoDespesa entity)
        {
            List<BusinessEntity.TipoDespesa> response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(" INSERT INTO TBODESPESA (CD_DESP_NOTA, DS_DESP_NOTA) VALUES (:Codigo, :Descricao)");

            using (DbCommand dbCommand = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(dbCommand, ":Codigo", DbType.String, entity.Codigo);
                this.DataBase.AddInParameter(dbCommand, ":Descricao", DbType.String, entity.Descricao);

                this.DataBase.ExecuteNonQuery(dbCommand);
            }
		}

	}
}